﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Windows.Controls;
using Mrsoft.SSC.Model;

namespace Mrsoft.SSC.DAL
{
    static class SecretCardDAL
    {

        static SecretCardDAL()
        {
            //创建数据表
            string sql = @"CREATE TABLE IF NOT EXISTS T_SecretCard(SerialId TEXT,ColumnId TEXT,RowId TEXT,KeyValue TEXT)";
            SqliteHelper.ExecuteNonQuery(sql);
        }

        public static SecretCard GetSecretCardById(string id)
        {
            var passwordList = new List<Password>();
            var sql = string.Format("select * from T_SecretCard where SerialId='{0}'", id);
            var table = SqliteHelper.ExecuteTable(sql);
            for (int i = 0; i < table.Rows.Count; i++)
            {
                var row = table.Rows[i];
                Password password = new Password
                {
                    SerialId = id,
                    ColumnId = Convert.ToString(row["ColumnId"]),
                    RowId = Convert.ToString(row["RowId"]),
                };
                password.KeyValue = row["KeyValue"].ToString();
                passwordList.Add(password);
            }
            return new SecretCard(id, passwordList);
        }

        public static List<string> GetAllSecretCardId()
        {
            var sql = "select SerialId from T_SecretCard where 1 group by SerialId";
            var table = SqliteHelper.ExecuteTable(sql);
            var list = new List<string>();
            for (int i = 0; i < table.Rows.Count; i++)
            {
                var row = table.Rows[i];
                list.Add(row["SerialId"].ToString());
            }
            return list;
        }

        public static void SaveSecretCard(SecretCard secretCard)
        {

            var sql = String.Format("DELETE FROM T_SecretCard WHERE SerialId='{0}'", secretCard.SerialId);
            SqliteHelper.ExecuteNonQuery(sql);

            var sb = new StringBuilder("INSERT INTO T_SecretCard(SerialId,ColumnId,RowId,KeyValue) VALUES");
            foreach (var password in secretCard.PasswordsList)
            {
                sb.AppendFormat("('{0}','{1}','{2}','{3}'),", password.SerialId, password.ColumnId, password.RowId,
                    password.KeyValue);
            }

            sql = sb.ToString().Substring(0, sb.Length - 1);
            SqliteHelper.ExecuteNonQuery(sql);
        }
    }
}
